#!/bin/sh
###编写hql语句进行数据的清洗
###org_info清洗规则：保留手机号的纯数字
###开发日期：2024-07-10
###开发人:zm
echo "---------------------清洗机构信息表的数据--------------------"
hive -v -e "set hive.exec.mode.local.auto=true; \
insert overwrite table xm02dw.org_info
Select 
orgid,
sortcode,
financial,
orgname,
orgcompletename,
orgengname,
orgrefname,
porgid,
orglevel,
regexp_replace(orgphone,'[^0-9]+','') orgphone, 
orgadress,
orgprovince,
orgzipcode,
network,
istree,
orgauthority,
orgstatus,
orgsquence,
orgtype,
orgdatadate,
oldorgid,
oldorgtype,
orgmergeid,
notes,
establishdate,
orgbegindate,
orgenddate,
inputuserid,
inputusername,
updatedate,
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')
From xm02ods.org_info;"
echo $?
num1=`hive -e "set hive.exec.mode.local.auto=true;select count(1) from xm02ods.org_info;"`
echo "------------------------ods层org_info表存在数据${num1}条--------------------"
num2=`hive -e "set hive.exec.mode.local.auto=true;select count(1) from xm02dw.org_info;"`
echo "------------------------清洗后dw层org_info表存在数据${num2}条--------------------"
if [ ${num1} -eq ${num2} ];then
echo "org_info表清洗成功！"
else
echo "org_info表清洗失败，请检查脚本"
fi
###编写hql语句进行数据的清洗
###org_info清洗规则：保留手机号的纯数字
###开发日期：2024-07-10
###开发人:zm
echo "---------------------清洗系统用户信息表的数据--------------------"
hive -v -e "set hive.exec.mode.local.auto=true; \
insert overwrite table xm02dw.sys_user_info
Select  
user_id,
user_name,
job,
dept_id,
password,
pwd_mod_status,
create_time,
mgr_id,
mgr_name,
org_id,
legal_rep,
gender,
age,
birth_dt,
cer_type,
cer_number,
birth_addr,
off_short_num,
email,
old_user_id,
user_type,
operator_id,
last_mai_user_id,
last_mai_time,
last_mai_ins_id,
user_status,
phone,
role_id,
re_dt,
remarks,
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')
From xm02ods.sys_user_info;"
echo $?
num3=`hive -e "set hive.exec.mode.local.auto=true;select count(1) from xm02ods.sys_user_info;"`
echo "------------------------ods层sys_user_info表存在数据${num3}条--------------------"
num4=`hive -e "set hive.exec.mode.local.auto=true;select count(1) from xm02dw.sys_user_info;"`
echo "------------------------清洗后dw层sys_user_info表存在数据${num4}条--------------------"
if [ ${num3} -eq ${num4} ];then
echo "sys_user_info表清洗成功！"
else
echo "sys_user_info表清洗失败，请检查脚本"
fi